﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MISAPP.Common;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_CIB002:DA_Matrix
    {
        public DA_CIB002()
        {
            tableName = "企金";
        }

        public override string[] GetDistinctField()
        {
            return new string[] { "YYYYMM", "CUR", "會計科目" };
        }


        public DataTable GetSummaryData(string p_compareField, string p_groupBy
            , bool p_checkAccount,string[] p_months,string p_unit
            ,bool p_rmGroup,string[] p_rmList)
        {
            string sql, rangeStatement, checkAccount,rmTbl,rmFld,rmON
                ,strNotFound,total1,total2,orderByStr;
            rmTbl = string.Empty;
            rmFld = string.Empty;
            rmON = string.Empty;
            strNotFound = string.Empty;
            total1 = string.Empty;
            total2 = string.Empty;
            orderByStr = string.Empty;

            rangeStatement = getRangeString(p_months, p_rmGroup, p_rmList);
            GetCriteriaString();
            checkAccount = GetExclusiveOfAccount(p_checkAccount);

            if (p_rmGroup == true)
            {
                StringBuilder sb = new StringBuilder();
                foreach (string rm in p_rmList)
                {
                    sb.AppendFormat(",'{0}'", rm);
                }                

                rmTbl = " LEFT OUTER JOIN RM A ON B.LNAC_ID = A.LNAC_ID";
                rmFld = ",A.RM_GROUP";
                rmON = "AND A.RM_GROUP = B.RM_GROUP";
                strNotFound = ",ISNULL(A.RM_GROUP,'NOT FOUND') RM_GROUP";
                string[] tmp = getTotalString();
                total1 = string.Format(tmp[0], CriteriaString
                    , checkAccount
                    , string.Format(" AND RM_GROUP IN({0}) ",sb.ToString().Substring(1))
                    );
                total2 = string.Format(tmp[1], CriteriaString
                    , checkAccount
                    , string.Format(" AND RM_GROUP IN({0}) ", sb.ToString().Substring(1))
                    );
                orderByStr = @"
ORDER BY CASE 
		WHEN RM_GROUP = '企業金融' THEN 1
		WHEN RM_GROUP = '商業金融' THEN 2
		WHEN RM_GROUP = '海外業務' THEN 3
		WHEN RM_GROUP = '新興金融' THEN 4
		WHEN RM_GROUP = '公庫' THEN 6
		WHEN RM_GROUP = 'NOT FOUND' THEN 7
		WHEN RM_GROUP = 'TOTAL' THEN 8		
	END,A.[Range]

";
            }

            sql = string.Format(getSummarySqlStatement()
                , rangeStatement
                , CriteriaString
                , checkAccount
                , p_unit
                , rowToColumn(p_months)
                , rmTbl
                , rmFld
                , rmON
                , strNotFound
                , total1
                , total2
                ) + orderByStr
                ;            

            AddParameter(sql);
            return getData();

        }

        public DataTable GetDetailData(string p_compareField, bool p_checkAccount, int p_rateIndex)
        {
            string sql, checkAccount;
            GetCriteriaString();
            checkAccount = GetExclusiveOfAccount(p_checkAccount);
            sql = string.Format(getDetailSqlStatement()
                , CriteriaString
                ,getRateString(p_rateIndex,p_compareField)
                ,checkAccount
                ,Utility.GetCIBDetailField(string.Empty));

            AddParameter(sql);
            return getData();
        }

        public override DataSet GetCriteria()
        {
            //DataSet ds = base.GetCriteria();
            DataSet ds = new DataSet();
            DA_MISParameter para = new DA_MISParameter();
            DataTable dt = para.GetParameter("CIB", "EXCLUDE");
            dt.TableName = "LNAC_ID";
            ds.Tables.Add(dt);
            DataTable dtRM = getMISParameter("RM_GROUP");
            dtRM.TableName = "RM_GROUP";
            ds.Tables.Add(Utility.GetYYYYMMRange(Convert.ToDateTime("2009-09-01")));
            ds.Tables.Add(Utility.GetCIBCurData());
            ds.Tables.Add(Utility.GetCIBAccountNoData());
            ds.Tables.Add(dtRM);
            return ds;
        }

        protected string getRateString(int p_rateIndex,string p_fld)
        {
            DataTable dt = getRangeDT();
            string str = dt.Rows[p_rateIndex]["fldvalue"].ToString();
            if (p_rateIndex == 0)
            {
                str = string.Format(str,"" , "");
            }

            string[] bound = str.Split(new char[] { ','});
            //0.25,0.5 AND (a.承作利率 >= 0.25 AND 0.5 < a.承作利率 )

            return string.Format(" AND ({0} >= {1} AND {0} < {2}  )"
                ,p_fld,bound[0],bound[1]);
        }

        protected string getSummarySqlStatement()
        {
            /*
             * {0} → 組合成 RANGE 的UNION TABLE
             * {1} → WHERE
             * {2} → 是否要排除公庫
             * {3} → 錢的計算改讓使用者選要以億或千萬為單位
             * {4} → 是要把ROW 轉成COL 用
             * {5} → 加入關連RM TABLE 用
             * {6} → RM TABLE 之GROUP BY EX：RM.RM_GROUP
             * {7} → RM TABLE 要有ON
             * {8} → 因為RM GROUP 中會有對應不到的NOT FOUND，所以加入檢查
             * {9} → 第一段UNION
             * {10} → 第二段UNION 含SUM
             */
            return @"
SELECT 
    A.[Range]
    {4}
    {6}
FROM(
SELECT 
	A.[Range] 
	,A.YYYYMM
    ,ROUND(A.TOT / {3},2) AS SUM_TOT
	,CAST( ROUND(A.TOT/B.TOT,4)*100 AS VARCHAR) + '%' AS [PERCENT]
    {6}
FROM (	
	SELECT  
		A.TXT AS [Range]
		,A.YYYYMM
		,SUM(ISNULL(B.均額,0)) TOT
        {6}
	FROM (
			{0}
	)A LEFT OUTER JOIN ( SELECT 
							B.承作利率
							,B.YYYYMM
							,B.均額
                            {8}
						FROM 企金 AS B  {5}  
						WHERE 均額 > 0
                        {1}
	                    {2} 

                        {9}

        ) B ON B.承作利率 >= A.L_BOUND AND B.承作利率 < A.R_BOUND
        AND A.YYYYMM = B.YYYYMM {7}
	GROUP BY A.YYYYMM ,A.TXT {6}
	)A INNER JOIN (
					SELECT 
				        YYYYMM
                        {8}
				        ,SUM(均額) AS TOT                 
				FROM [MISDB].[dbo].[企金] B {5}  
				WHERE 均額 > 0
                    {1}
                    {2}
				GROUP BY YYYYMM {6}
                                                
                {10}
				)B
ON A.YYYYMM = B.YYYYMM {7}				
) A
GROUP BY A.[Range] {6}
";
          
        }

        protected string getDetailSqlStatement()
        {
            //0 是一般條件
            //1 是利率，要找出目前是落在那一個條件式
            //2 排除公庫

            return @"SELECT {3}
                     FROM [MISDB].[dbo].[企金] 
                     WHERE 均額 > 0 
                           {0}
                           {1}
                           {2} 
";
        }

        protected DataTable getRangeDT()
        {
            return getMISParameter("RANGE");
        }

        protected DataTable getMISParameter(string p_para)
        {
            return Utility.GetParameterByLevel("CIB", p_para);
        }

        
        protected string getRangeString(string[] p_months,bool p_rmGroup
            ,string[] p_rmList)
        {
            DataTable dt = getRangeDT();
                       
            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                if (i == 0)
                {
                    string str = row["fldvalue"].ToString();
                    //0.0 L_BOUND ,0.25 R_BOUND
                    sb.AppendFormat("SELECT {0} , '{1}' AS TXT  \r\n"
                        , string.Format(str,"L_BOUND","R_BOUND")
                        ,row["fldname"].ToString());
                }
                else
                {
                    sb.AppendFormat("UNION SELECT {0},'{1}'  \r\n"
                        , row["fldvalue"].ToString()
                        , row["fldname"].ToString());
                }              
                i++;
            }

            if (p_rmGroup == true)
            {

                foreach (string mon in p_months)
                {
                    /*
                    if (sb2.ToString().Length.Equals(0))
                    {
                        sb2.AppendFormat("#SELECT '{0}' AS YYYYMM ,'{1}' AS RM_GROUP \r\n"
                            , mon, "TOTAL");                    
                    }
                    */
                    sb2.AppendFormat("#SELECT '{0}' AS YYYYMM ,'{1}' AS RM_GROUP \r\n"
                        , mon, "TOTAL");                    

                    foreach (string rm in p_rmList)
                    {
                        sb2.AppendFormat("#SELECT '{0}' AS YYYYMM ,'{1}' AS RM_GROUP \r\n"
                            , mon, rm);                    
                    }
                }                        
            }
            else
            {
                foreach (string mon in p_months)
                {
                    sb2.AppendFormat("#SELECT '{0}' AS YYYYMM \r\n", mon);
                }
            }
           
            return string.Format( @"SELECT * 
                     FROM({0}
                         ) AS A CROSS JOIN ({1}
                                            ) AS B
					  ",sb.ToString(),sb2.ToString().Substring(1).Replace("#"," UNION "));
        }

        protected string[] getOriginColumns()
        { return new string[] { "Range", "SUM_TOT", "PERCENT" };}

    
        /// <summary>
        /// 依所選的月份，產生出沒有資料空的表格
        /// </summary>
        /// <param name="p_months"></param>
        /// <returns></returns>
        protected DataTable createCompareTable(string[] p_months)
        {
            string[] originColumns = getOriginColumns();
            Type[] colTypeArray = { typeof(string),typeof(double),typeof(string)};
            DataTable dt = new DataTable();
            for (int i = 0; i < p_months.Length; i++)
            {
                for (int x = 0; x < originColumns.Length ; x++)
                {
                    DataColumn col = new DataColumn();
                    col.ColumnName = string.Format("{0}_{1}", originColumns[x], p_months[i]);
                    col.DataType = colTypeArray[x];
                    dt.Columns.Add(col);
                }                               
            }
            return dt;
        }

        protected string rowToColumn(string[] p_months)
        {
            string sumString, percentString;
            sumString = ",MAX(CASE WHEN A.YYYYMM ='{0}' THEN CONVERT(NVARCHAR,CAST(A.SUM_TOT AS MONEY),1) ELSE '0' END) AS [{0}_SUM_TOT] \r\n";
            percentString = ",MAX(CASE WHEN A.YYYYMM ='{0}' THEN A.[PERCENT] ELSE '0%' END) AS [{0}_PERCENT] \r\n";
            StringBuilder sb = new StringBuilder();
            foreach(string mon in p_months.Reverse())
            {
                sb.AppendFormat(sumString, mon);
                sb.AppendFormat(percentString, mon);            
            }
            return sb.ToString();
        }

        protected string[] getTotalString()
        { 
            return new []
            {@"			UNION ALL
                        SELECT 
							B.承作利率
							,B.YYYYMM
							,B.均額
                            ,'TOTAL' RM_GROUP
						FROM 企金 AS B LEFT OUTER JOIN RM A ON B.LNAC_ID = A.LNAC_ID
						WHERE 均額 > 0
							{0}
							{1}
                            {2}
"
                ,@"
                    UNION ALL
					
					SELECT 
				        YYYYMM
                        ,'TOTAL' RM_GROUP
				        ,SUM(均額) AS TOT                 
					FROM [MISDB].[dbo].[企金] B  LEFT OUTER JOIN RM A ON B.LNAC_ID = A.LNAC_ID  
					WHERE 均額 > 0
						{0} 
						{1}
                        {2}
					GROUP BY YYYYMM 
"};
        }
    }
}
